<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
	<xsl:template match="Class">
		<xsl:variable name="Name" select="Name"/>
		<xsl:variable name="SingleCount" select="count(ClassAttribs/ClassAttributes[CType='single'])"/>

PRINT 'Working on object [<xsl:value-of select="Name"/>]'


CREATE TABLE [<xsl:value-of select="Name"/>] (
	[<xsl:value-of select="Name"/>ID] [uniqueidentifier] NOT NULL,
	[Created] [datetime] NOT NULL,
	[Modified] [datetime] NOT NULL,
		<xsl:for-each select="ClassAttribs/ClassAttributes">
			<xsl:if test="CType='single'">
	[<xsl:value-of select="Name"/>] <xsl:value-of select="SqlType"/> NOT NULL,
			</xsl:if>
		</xsl:for-each>
	CONSTRAINT [PK_<xsl:value-of select="Name"/>] PRIMARY KEY CLUSTERED ( [<xsl:value-of select="Name"/>ID] ) ON [PRIMARY]
) ON [PRIMARY]










SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>SelectAll AS
	SELECT * FROM [<xsl:value-of select="Name"/>] <xsl:for-each select="ClassAttribs/ClassAttributes[ToString='true']">order by [<xsl:value-of select="Name"/>]</xsl:for-each>
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>CountAll AS
	SELECT COUNT(*) FROM [<xsl:value-of select="Name"/>]
GO


CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Select (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	SELECT *
	FROM [<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
	
GO




CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Delete (@<xsl:value-of select="Name"/>ID uniqueidentifier) AS
	DELETE FROM [<xsl:value-of select="Name"/>] WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
GO


CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Insert (@<xsl:value-of select="Name"/>ID uniqueidentifier,
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		<xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	</xsl:if>
</xsl:for-each>
AS
	INSERT INTO [<xsl:value-of select="Name"/>]([<xsl:value-of select="Name"/>ID],[created],[modified],
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>])</xsl:if>
		<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>],</xsl:if>
	</xsl:if>
</xsl:for-each>

	VALUES(@<xsl:value-of select="Name"/>ID,getDate(),getDate(),
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/>)</xsl:if>
		<xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/>,</xsl:if>	
	</xsl:if>
</xsl:for-each>
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="HeaderInfo/Prefix"/>_<xsl:value-of select="Name"/>Update (@<xsl:value-of select="Name"/>ID uniqueidentifier,
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>)</xsl:if>
		<xsl:if test="position()!=$SingleCount">@<xsl:value-of select="Name"/><xsl:text> </xsl:text><xsl:value-of select="SqlSPType"/>,</xsl:if>
	</xsl:if>
</xsl:for-each>
AS
	UPDATE [<xsl:value-of select="Name"/>] SET [modified]=getDate(),
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
		<xsl:if test="position()=$SingleCount">[<xsl:value-of select="Name"/>]=@<xsl:value-of select="Name"/></xsl:if>
		<xsl:if test="position()!=$SingleCount">[<xsl:value-of select="Name"/>]=@<xsl:value-of select="Name"/>,</xsl:if>
	</xsl:if>
</xsl:for-each>
WHERE [<xsl:value-of select="Name"/>ID]=@<xsl:value-of select="Name"/>ID
GO



<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="ToString='true'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>Search(@searchString nvarchar(255))
AS
	SELECT <xsl:value-of select="$Name"/>ID FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>] like '%' + @searchString + '%'
GO		
	</xsl:if>
</xsl:for-each>
        
        
-- DEFINED PROCEDURES FOR CONTENT
<xsl:for-each select="ClassAttribs/ClassAttributes">
	<xsl:if test="CType='single'">
        <xsl:choose>
            <xsl:when test="Type='bool'">
                
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val tinyint)
AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO
            </xsl:when>
            
            <xsl:when test="Type='DateTime'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Between(@start_ datetime, @end_ datetime)
AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@start_ and [<xsl:value-of select="Name"/>]&lt;=@end_
GO 

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Before(@val datetime)
AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
GO 

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_After(@val datetime)
AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
GO 

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val datetime)
AS
    SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO 

            </xsl:when>
            
            <xsl:when test="Type='int'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val int)
AS
SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO  

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan(@val int)
AS
SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan(@val int)
AS
SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
GO
            </xsl:when>
            
            <xsl:when test="Type='double'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val float)
AS
SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO  

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_GreaterThan(@val float)
AS
SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&gt;=@val
GO

CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_LessThan(@val float)
AS
SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]&lt;=@val
GO
            </xsl:when>
            
            
            <xsl:when test="Type='string' and SqlSPType!='ntext'">
CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>(@val nvarchar(255))
AS
SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>]=@val
GO                       


CREATE PROCEDURE GENSP_<xsl:value-of select="//HeaderInfo/Prefix"/>_<xsl:value-of select="$Name"/>_<xsl:value-of select="Name"/>_Like(@val nvarchar(255))
AS
SELECT * FROM [<xsl:value-of select="$Name"/>] where [<xsl:value-of select="Name"/>] like '%' + @val + '%'
GO   
            </xsl:when>
        </xsl:choose>
    </xsl:if>
</xsl:for-each>



SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO




































































PRINT 'End working on object [<xsl:value-of select="Name"/>]'
PRINT 'End working on object [<xsl:value-of select="Name"/>]'
PRINT 'End working on object [<xsl:value-of select="Name"/>]'
PRINT 'End working on object [<xsl:value-of select="Name"/>]'
PRINT 'End working on object [<xsl:value-of select="Name"/>]'

	
	</xsl:template>
</xsl:stylesheet>

  